package com.geline.luckysheet.util;

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import lombok.extern.slf4j.Slf4j;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

/**
 * Excel工具类
 * @author: mengxin
 * @date: 2022/10/12 11:12
 */
@Slf4j
public class EasyExcelUtil {

    /**
     * 导出Map+List
     * @param excelWriter
     * @param dataMap
     * @param dataList
     */
    public static void complexFill(ExcelWriter excelWriter, Map<String, Object> dataMap, List dataList){
        try {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            if(dataMap != null){
                excelWriter.fill(dataMap, writeSheet);
            }
            if(dataList!=null && dataList.size()>0){
                FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                excelWriter.fill(dataList, fillConfig, writeSheet);
            }
        } finally {
            // 千万别忘记close 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.close();
            }
        }
    }

    /**
     * 填充复杂数据：支持Map+List
     * @param excelWriter  EasyExcel.write(fileName).withTemplate(templateFileName).build();
     * @param data
     */
    public static void complexFillManyList(ExcelWriter excelWriter, Map<String, Object> data, Map<String, List> listMap){
        try {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            excelWriter.fill(data, writeSheet);
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            for(String key : listMap.keySet()){
                List value = listMap.get(key);
                if(value != null){
                    if(StrUtil.isBlank(key)){
                        excelWriter.fill(value, fillConfig, writeSheet);
                    }else {
                        excelWriter.fill(new FillWrapper(key, value), fillConfig, writeSheet);
                    }
                }
            }
        } finally {
            // 千万别忘记close 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.close();
            }
        }
    }

    public static void downExcelByComplexFill(HttpServletResponse response, InputStream template, String filename,
                                              Map<String, Object> data, List dataList) {
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String name = URLEncoder.encode(filename, "UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="+name+".xlsx");

            //获取输出流
            ServletOutputStream outputStream = response.getOutputStream();
            ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(template).
                    registerConverter(new BaseDateConverter.JavaSqlDateConverter()).build();
            complexFill(excelWriter, data, dataList);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 按excel模板导出
     *  模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
     *  参考：https://blog.csdn.net/qq_40744629/article/details/114665284
     * @param response
     * @param template 模板文件流
     * @param filename 导出文件名，无后缀
     * @param data   Map对象填充，使用方式：{key1} {key2}
     * @param listMap  List填充，使用方式：{key.name} {key.createTime}　参考：https://blog.csdn.net/menghunzhihun/article/details/123623843
     */
    public static void downExcelByComplexFill(HttpServletResponse response, InputStream template, String filename,
                                              Map<String, Object> data, Map<String, List> listMap) {
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String name = URLEncoder.encode(filename, "UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="+name+".xlsx");

            //获取输出流
            ServletOutputStream outputStream = response.getOutputStream();
            ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(template).
                    registerConverter(new BaseDateConverter.JavaSqlDateConverter()).build();
            complexFillManyList(excelWriter, data, listMap);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 按Class导出excel
     * @param response
     * @param listData
     * @param filename
     * @param clazz
     */
    public static void downExcelByClass(HttpServletResponse response, List listData, String filename, Class clazz) {
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String name = URLEncoder.encode(filename, "UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="+name+".xlsx");

            //获取输出流
            ServletOutputStream outputStream = response.getOutputStream();
            //获取工作簿对象
            ExcelWriter excelWriter = EasyExcel.write(outputStream, clazz).build();

            Integer sheetnum = 1;//获取工作表数量
            int size = listData.size();//集合大小
            if (size < 1000) {//这里控制每个工作簿1000条数据
                sheetnum = 1;
            }else {
                sheetnum = size%1000==0 ? size/1000 : size/1000+1;
            }

            //去调用写入,这里我调用了sheetnum次，实际使用时根据数据库分页的总的页数来。这里最终会写到sheetnum个sheet里面
            List subList = null;
            for(int i = 0; i < sheetnum; i++) {
                // 每次都要创建writeSheet 这里注意必须指定sheetNo
                WriteSheet writeSheet = EasyExcel.writerSheet(i,filename+i).build();
                // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
                subList = listData.subList(i*1000, i==sheetnum-1 ? size : (i+1)*1000);
                excelWriter.write(subList, writeSheet);
            }
            //finish 会帮忙关闭流
            excelWriter.finish();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}
